package cn.itcast.jsjwl.dao;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import cn.itcast.jsjwl.domain.Notice;
import cn.itcast.jsjwl.domain.Source;
import cn.itcast.jsjwl.utils.JDBCUtils;

public class NoticeDao {

    public int findTotalRecords() throws SQLException {
	   String sql = "SELECT COUNT(*) FROM notice";
	   QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
	   Long num=(Long)qr.query(sql, new ScalarHandler());
	   return num.intValue();
    }

	public List<Notice> findNoticeWithPage(int startIndex, int pageSize) throws SQLException {
		String sql = "SELECT * FROM notice ORDER BY time DESC LIMIT ? , ?";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		return qr.query(sql, new BeanListHandler<Notice>(Notice.class),startIndex,pageSize);
	}

	public void addNotice(Notice notice) throws SQLException {
		String sql="INSERT INTO notice (title,content,upname,time) VALUES( ? , ? , ? , ? );";
		Object[] params= {notice.getTitle(),notice.getContent(),notice.getUpname(),notice.getTime()};
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		qr.update(sql,params);
		
	}

	public void deleteNotice(String nId) throws SQLException {
		String sql="delete FROM notice WHERE nid=?";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		qr.update(sql,nId);
		
	}

	public List<Notice> findNoticeWithPageByTeacher(int startIndex, int pageSize) throws SQLException {
		String sql = "SELECT * FROM notice ORDER BY time DESC LIMIT ? , ?";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		return qr.query(sql, new BeanListHandler<Notice>(Notice.class),startIndex,pageSize);
	}

	public List<Notice> findNoticeWithPageByStu(int startIndex, int pageSize) throws SQLException {
		String sql = "SELECT * FROM notice ORDER BY time DESC LIMIT ? , ?";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		return qr.query(sql, new BeanListHandler<Notice>(Notice.class),startIndex,pageSize);
	}

	public Notice findNoticeByNidByStu(String nId) throws SQLException {
		String sql="SELECT * FROM notice WHERE nid=?";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		return qr.query(sql, new BeanHandler<Notice>(Notice.class),nId);
	}

	public Notice findNoticeByNidByTeacher(String nId) throws SQLException {
		String sql="SELECT * FROM notice WHERE nid=?";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		return qr.query(sql, new BeanHandler<Notice>(Notice.class),nId);
	}

}
